﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Transactions;

namespace ArmoryAPI
{
    /// <summary>
    /// Provides functionality to interact with the cached data in the armoryapi database
    /// </summary>
    internal class DatabaseRequest
    {
        #region Members

            private String _connectionString;
            private BattleNetRegion _region;
            private BattleNetLocale _locale;

        #endregion

        #region Instance Methods

            /// <summary>
        /// Fetches the requested record from the database into an Achievement
        /// </summary>
        /// <param name="id">The unique identifier of the achievement</param>
        /// <returns>Achievement</returns>
            public Achievement GetAchievementByID(int id)
            {
                Achievement retval = new Achievement();

                try
                {
                    using (SqlConnection connection = new SqlConnection(_connectionString))
                    {
                        connection.Open();

                        using (SqlCommand command = new SqlCommand("SELECT * FROM achievements WHERE id = @id", connection))
                        {
                            command.Parameters.AddWithValue("@id", id);

                            using (SqlDataReader reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    retval = new Achievement((int)reader["id"], (string)reader["title"], (int)reader["points"], (string)reader["description"]);
                                }
                            }
                        }

                        connection.Close();
                    }
                }
                catch
                {
                }

                return retval;
            }

            /// <summary>
            /// Fetches all the records in the achievements table into an ICollection of Achievements
            /// </summary>
            /// <returns>An ICollection of Achievements</returns>
            public ICollection<Achievement> GetAllAchievements()
            {
                List<Achievement> retval = new List<Achievement>();

                try
                {
                    using (SqlConnection connection = new SqlConnection(_connectionString))
                    {
                        connection.Open();

                        using (SqlCommand command = new SqlCommand("SELECT * FROM achievements", connection))
                        {
                            using (SqlDataReader reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    retval.Add(new Achievement((int)reader["id"], (string)reader["title"], (int)reader["points"], (string)reader["description"]));
                                }
                            }
                        }

                        connection.Close();
                    }
                }
                catch { }

                return retval;
            }

            /// <summary>
            /// Updates the achievements data with the latest list of achievements from the Battle.net API using the locale specified
            /// </summary>
            /// <returns>True on success, false on error</returns>
            public bool RefreshAchievements()
            {
                // Get the latest achievements from Battle.net
                bool retval = true;
                WebRequest request = new WebRequest(_region, _locale);
                List<Achievement> achievements = (List<Achievement>)request.GetAllAchievements();

                using (TransactionScope scope = new TransactionScope())
                {
                    try
                    {
                        using (SqlConnection connection = new SqlConnection(_connectionString))
                        {
                            connection.Open();

                            using (SqlCommand command = new SqlCommand("DELETE FROM achievements", connection))
                            {
                                command.ExecuteNonQuery();
                            }

                            using (SqlCommand command = new SqlCommand("INSERT INTO achievements (id, title, points, description) VALUES (@id, @title, @points, @description)", connection))
                            {
                                foreach (Achievement achievement in achievements)
                                {
                                    command.Parameters.Clear();
                                    command.Parameters.AddWithValue("@id", achievement.ID);
                                    command.Parameters.AddWithValue("@title", achievement.Title);
                                    command.Parameters.AddWithValue("@points", achievement.Points);
                                    command.Parameters.AddWithValue("@description", achievement.Description);

                                    command.ExecuteNonQuery();
                                }
                            }

                            connection.Close();
                        }

                        scope.Complete();
                    }
                    catch
                    {
                        retval = false;
                    }
                }

                return retval;
            }

            /// <summary>
            /// Updates the last refreshed timestamp against a character in the database with the current date and time
            /// </summary>
            /// <param name="character">The name of the character</param>
            /// <param name="realm">The name of the realm in either normal or friendly format</param>
            private void UpdateCharacterRefreshTimestamp(String character, String realm)
            {
                realm = realm.Replace("'", "").Replace("`", "").Replace(" ", "-");
              
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("UPDATE characters SET last_refresh = @last_refresh WHERE character = @character AND friendlyRealm = @friendlyRealm", connection))
                    {
                        command.Parameters.AddWithValue("@last_refresh", DateTime.Now);
                        command.Parameters.AddWithValue("@friendlyRealm", realm);
                        command.Parameters.AddWithValue("@character", character);

                        command.ExecuteNonQuery();
                    }

                    connection.Close();
                }
            }

            /// <summary>
            /// Checks to see if the guild already exists, if not inserts a record and in either case returns the ID number
            /// </summary>
            /// <param name="guild">The guild to insert</param>
            /// <returns>The ID number of the guild</returns>
            /// <remarks>This method is used primarily with SaveAndReturnCharacter as opposed to the GetGuild methods for direct guild queries</remarks>
            private int CreateGuildRecord(Guild guild)
            {
                int retval = 0;

                if (guild != null)
                {
                    using (SqlConnection connection = new SqlConnection(_connectionString))
                    {
                        connection.Open();

                        using (SqlCommand command = new SqlCommand("SELECT id FROM guilds WHERE region = @region AND guildName = @name AND realm = @realm", connection))
                        {
                            command.Parameters.AddWithValue("@region", _region.ToString());
                            command.Parameters.AddWithValue("@name", guild.Name);
                            command.Parameters.AddWithValue("@realm", guild.Realm);

                            object result = command.ExecuteScalar();

                            if (result != null)
                            {
                                retval = (int)result;
                            }
                        }

                        if (retval == 0)
                        {
                            using (SqlCommand command = new SqlCommand(@"INSERT INTO guilds (guildName, realm, friendlyRealm, region, guildLevel, members, achievementPoints, last_refresh) VALUES (@guildName, @realm, @friendlyRealm, @region, @guildLevel, @members, @achievementPoints, @lastRefresh); SELECT SCOPE_IDENTITY()", connection))
                            {
                                command.Parameters.AddWithValue("@guildName", guild.Name);
                                command.Parameters.AddWithValue("@realm", guild.Realm);
                                command.Parameters.AddWithValue("@friendlyRealm", guild.Realm);
                                command.Parameters.AddWithValue("@region", _region.ToString());
                                command.Parameters.AddWithValue("@guildLevel", guild.Level);
                                command.Parameters.AddWithValue("@members", guild.MemberCount);
                                command.Parameters.AddWithValue("@achievementPoints", guild.AchievementPoints);
                                command.Parameters.AddWithValue("@lastRefresh", DateTime.Now);

                                retval = Int32.Parse(command.ExecuteScalar().ToString());
                            }
                        }

                        connection.Close();
                    }
                }

                return retval;
            }

            /// <summary>
            /// Fetches the guild record from the database and parses it into a Guild object
            /// </summary>
            /// <param name="id">The unique identifier of the guild</param>
            /// <param name="expiration">The time in minutes that the cached data should remain valid</param>
            /// <param name="autoRefresh">Indicates whether or not data should be auto-refreshed once it has expired</param>
            /// <returns>A Guild</returns>
            private Guild GetGuild(int id, int expiration, bool autoRefresh)
            {
                String name = String.Empty;
                String realm = String.Empty;

                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("SELECT guildName, realm FROM guilds WHERE id = @id", connection))
                    {
                        command.Parameters.AddWithValue("@id", id);

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                name = (string)reader["guildName"];
                                realm = (string)reader["realm"];
                            }
                        }
                    }

                    connection.Close();
                }

                return this.GetGuild(name, realm, expiration, autoRefresh);
            }

            /// <summary>
            /// Fetches the guild record from the database and parses it into a Guild object
            /// </summary>
            /// <param name="name">The name of the guild</param>
            /// <param name="realm">The name of the realm the guild is located on in standard format</param>
            /// <param name="expirationThreshold">The time in minutes that the cached data should remain valid</param>
            /// <param name="autoRefresh">Indicates whether or not data should be auto-refreshed once it has expired</param>
            /// <returns>A Guild</returns>
            public Guild GetGuild(String name, String realm, int expirationThreshold, bool autoRefresh)
            {
                Guild retval = new Guild();

                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("SELECT * FROM guilds WHERE guildName = @name AND realm = @realm AND region = @region", connection))
                    {
                        command.Parameters.AddWithValue("@name", name);
                        command.Parameters.AddWithValue("@realm", realm);
                        command.Parameters.AddWithValue("@region", _region.ToString());

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            // If a record exists for this guild and auto-refreshing is enabled refresh the data if it has expired
                            // and construct the object. If no record exists for the guild fetch it via the RESTful API and store it
                            if (reader.Read())
                            {
                                bool hasExpired;

                                hasExpired = (((DateTime)reader["last_refresh"]).AddMinutes(expirationThreshold) < DateTime.Now);

                                if (hasExpired && autoRefresh)
                                {
                                    Guild updated = this.SaveAndReturnGuild(name, realm, false);
                                    if (updated != null)
                                    {
                                        retval = updated;
                                    }
                                    else
                                    {
                                        retval = new Guild((string)reader["guildName"], (string)reader["realm"], (short)reader["guildLevel"], (int)reader["members"], (int)reader["achievementPoints"]);
                                    }
                                }
                                else
                                {
                                    retval = new Guild((string)reader["guildName"], (string)reader["realm"], (short)reader["guildLevel"], (int)reader["members"], (int)reader["achievementPoints"]);
                                }
                            }
                            else
                            {
                                retval = this.SaveAndReturnGuild(name, realm, true);
                            }
                        }
                    }

                    connection.Close();
                }

                return retval;
            }

            /// <summary>
            /// Returns the unique identifier for the specified character
            /// </summary>
            /// <param name="character">The character's name</param>
            /// <param name="realm">The realm name in standard or friendly format</param>
            /// <returns>A 32-bit integer</returns>
            private int GetCharacterID(String character, String realm)
            {
                int retval = -1;
                realm = realm.Replace("`", String.Empty).Replace("'", String.Empty).Replace(" ", "-");

                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("SELECT id FROM characters WHERE character = @character AND friendlyRealm = @realm AND region = @region", connection))
                    {
                        command.Parameters.AddWithValue("@character", character);
                        command.Parameters.AddWithValue("@realm", realm);
                        command.Parameters.AddWithValue("@region", _region.ToString());

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                retval = (int) reader["id"];
                            }
                        }
                    }

                    connection.Close();
                }

                return retval;
            }

            /// <summary>
            /// Removes all the existing reords in the character_titles table and inserts the latest title data from the RESTful API
            /// </summary>
            /// <param name="characterID">The UID of the character to update</param>
            /// <param name="titles">The collection of titles to insert into the database</param>
            private void UpdateCharacterTitles(int characterID, ICollection<Title> titles)
            {
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("DELETE FROM character_titles WHERE character_id = @characterID", connection))
                    {
                        command.Parameters.AddWithValue("@characterID", characterID);
                        command.ExecuteNonQuery();
                    }

                    using (SqlCommand command = new SqlCommand("INSERT INTO character_titles (character_id, title) VALUES (@characterID, @title)", connection))
                    {
                        foreach (Title title in titles)
                        {
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@characterID", characterID);
                            command.Parameters.AddWithValue("@title", title.PlayerTitle);

                            command.ExecuteNonQuery();
                        }
                    }

                    connection.Close();
                }
            }

            /// <summary>
            /// Removes all the existing records in the character_achievements table and inserts the latest achievement data from the RESTful API
            /// </summary>
            /// <param name="characterID">The UID of the character to update</param>
            /// <param name="achievements">The collection of achievements to insert into the database</param>
            private void UpdateCharacterAchievements(int characterID, ICollection<UnlockedAchievement> achievements)
            {
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("DELETE FROM character_achievements WHERE character_id = @characterID", connection))
                    {
                        command.Parameters.AddWithValue("@characterID", characterID);
                        command.ExecuteNonQuery();
                    }

                    using (SqlCommand command = new SqlCommand("INSERT INTO character_achievements (character_id, achievement_id, unlock_datetime) VALUES (@characterID, @achievementID, @unlockDateTime)", connection))
                    {
                        foreach (UnlockedAchievement achievement in achievements)
                        {
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@characterID", characterID);
                            command.Parameters.AddWithValue("@achievementID", achievement.ID);
                            command.Parameters.AddWithValue("@unlockDateTime", achievement.Unlocked);

                            command.ExecuteNonQuery();
                        }
                    }

                    connection.Close();
                }
            }

            /// <summary>
            /// Creates a Character object using the Battle.net RESTful API, saves the data into the database and returns the object
            /// </summary>
            /// <param name="character">The character name</param>
            /// <param name="realm">The realm name in either standard or friendly format</param>
            /// <param name="insert">True to insert the record, false to update an existing record</param>
            /// <returns>A Character</returns>
            private Character SaveAndReturnCharacter(String character, String realm, bool insert)
            {
                realm = realm.Replace("'", "").Replace("`", "").Replace(" ", "-");
                WebRequest request = new WebRequest(_region, _locale);
                Character retval;
                int guildID;

                // Construct a new character object based on the latest data
                // from Battle.net, if an exception is thrown then there have
                // been no modifications, and thus we don't need to update the
                // database, so return null 
                try
                {
                    retval = request.GetCharacter(character, realm, this);
                }
                catch
                {
                    this.UpdateCharacterRefreshTimestamp(character, realm);
                    return null;
                }

                String sqlString;

                // Generate the appropriate query based on whether or not the record is being inserted or updated
                if (insert)
                {
                    sqlString = "INSERT INTO characters " +
                                "(region, character, friendlyRealm, realm, class, race, gender, player_level, achievementPoints, thumbnail, last_modified, last_refresh, guildID) " +
                                "VALUES (@region, @character, @friendlyRealm, @realm, @class, @race, @gender, @player_level, @achievementPoints, @thumbnail, @last_modified, @last_refresh, @guildID)";
                }
                else
                {
                    sqlString = "UPDATE characters SET region = @region, character = @character, friendlyRealm = @friendlyRealm, realm = @realm, " +
                                "gender = @gender, player_level = @player_level, achievementPoints = @achievementPoints, thumbnail = @thumbnail, " +
                                "last_modified = @last_modified, last_refresh = @last_refresh, guildID = @guildID WHERE (region = @region AND character = @character AND friendlyRealm = @friendlyRealm)";
                }

                guildID = this.CreateGuildRecord(retval.Guild);

                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand(sqlString, connection))
                    {
                        command.Parameters.AddWithValue("@region", _region.ToString());
                        command.Parameters.AddWithValue("@character", retval.Name);
                        command.Parameters.AddWithValue("@friendlyRealm", realm);
                        command.Parameters.AddWithValue("@class", retval.ClassID);
                        command.Parameters.AddWithValue("@race", retval.RaceID);
                        command.Parameters.AddWithValue("@realm", retval.Realm);
                        command.Parameters.AddWithValue("@gender", retval.GenderBitFlag);
                        command.Parameters.AddWithValue("@player_level", retval.Level);
                        command.Parameters.AddWithValue("@achievementPoints", retval.AchievementPoints);
                        command.Parameters.AddWithValue("@thumbnail", retval.RelativeThumbnail);
                        command.Parameters.AddWithValue("@last_modified", retval.LastModified);
                        command.Parameters.AddWithValue("@guildID", guildID);
                        command.Parameters.AddWithValue("@last_refresh", DateTime.Now);

                        command.ExecuteNonQuery();
                    }

                    connection.Close();
                }

                this.UpdateCharacterAchievements(this.GetCharacterID(character, realm), retval.Achievements);
                this.UpdateCharacterTitles(this.GetCharacterID(character, realm), retval.Titles);

                return retval;
            }

            /// <summary>
            /// Creates a Guild object using the Battle.net RESTful API, saves the data into the database and returns the object
            /// </summary>
            /// <param name="name">The name of the guild</param>
            /// <param name="realm">The realm name in either standard or friendly format</param>
            /// <param name="insert">True to insert the record, false to update an existing record</param>
            /// <returns>A Guild</returns>
            private Guild SaveAndReturnGuild(String name, String realm, bool insert)
            {
                realm = realm.Replace("'", "").Replace("`", "").Replace(" ", "-");
                WebRequest request = new WebRequest(_region, _locale);
                Guild retval;

                // Construct a new guild object based on the latest data
                // from Battle.net
                retval = request.GetGuild(name, realm);

                if (!insert)
                {
                    using (SqlConnection connection = new SqlConnection(_connectionString))
                    {
                        connection.Open();

                        using (SqlCommand command = new SqlCommand("UPDATE guilds SET guildLevel = @guildLevel, members = @members, achievementPoints = @achievementPoints, last_refresh = @lastRefresh WHERE guildName = @name AND friendlyRealm = @realm AND region = @region", connection))
                        {
                            command.Parameters.AddWithValue("@guildLevel", retval.Level);
                            command.Parameters.AddWithValue("@members", retval.MemberCount);
                            command.Parameters.AddWithValue("@achievementPoints", retval.AchievementPoints);
                            command.Parameters.AddWithValue("@lastRefresh", DateTime.Now);
                            command.Parameters.AddWithValue("@name", retval.Name);
                            command.Parameters.AddWithValue("@realm", realm);
                            command.Parameters.AddWithValue("@region", _region.ToString());

                            command.ExecuteNonQuery();
                        }

                        connection.Close();
                    }
                }
                else
                {
                    this.CreateGuildRecord(retval);
                }
                
                return retval;
            }

            /// <summary>
            /// Gets an ICollection of Achievements that the character has unlocked
            /// </summary>
            /// <param name="characterID">The UID of the character to fetch the achievements for</param>
            /// <returns>An ICollection of Achievement objects</returns>
            public ICollection<UnlockedAchievement> GetCharacterAchievements(int characterID)
            {
                ICollection<UnlockedAchievement> retval = new List<UnlockedAchievement>();

                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("SELECT achievements.*, unlock_datetime FROM character_achievements INNER JOIN achievements ON achievements.id = achievement_id WHERE character_id = @characterID", connection))
                    {
                        command.Parameters.AddWithValue("@characterID", characterID);

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                retval.Add(new UnlockedAchievement((int)reader["id"], (string)reader["title"], (int)reader["points"], (string)reader["description"], (DateTime)reader["unlock_datetime"]));
                            }
                        }
                    }

                    connection.Close();
                }

                return retval;
            }

            /// <summary>
            /// Get an ICollection of Titles that the character has unlocked
            /// </summary>
            /// <param name="characterID">The UID of the character to fetch the achievements for</param>
            /// <returns>An ICollection of Titles</returns>
            public ICollection<Title> GetCharacterTitles(int characterID)
            {
                ICollection<Title> retval = new List<Title>();

                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("SELECT title FROM character_titles WHERE character_id = @characterID", connection))
                    {
                        command.Parameters.AddWithValue("@characterID", characterID);

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                retval.Add(new Title((String)reader["title"]));
                            }
                        }
                    }

                    connection.Close();
                }

                return retval;
            }

            /// <summary>
            /// Fetch a character from the database
            /// </summary>
            /// <param name="character">The character name</param>
            /// <param name="realm">The realm name in either standard or friendly format</param>
            /// <param name="expirationThreshold">The cache expiration threshold in minutes</param>
            /// <param name="autoRefresh">True to auto refresh the data if it has expired, or false to not refresh</param>
            /// <returns>A Character</returns>
            public Character GetCharacter(String character, String realm, int expirationThreshold, bool autoRefresh)
            {
                Character retval = new Character();
                realm = realm.Replace("'", "").Replace("`", "").Replace(" ", "-");

                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("SELECT * FROM characters WHERE region = @region AND character = @character AND friendlyRealm = @realm", connection))
                    {
                        command.Parameters.AddWithValue("@region", _region.ToString());
                        command.Parameters.AddWithValue("@character", character);
                        command.Parameters.AddWithValue("@realm", realm);

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            // If a record exists for this character and auto-refreshing is enabled refresh the data if it has expired
                            // and construct the object. If no record exists for the character fetch it via the RESTful API and store it
                            if (reader.Read())
                            {
                                DateTime lastModified = (DateTime)reader["last_modified"];
                                bool hasExpired;

                                hasExpired = (((DateTime)reader["last_refresh"]).AddMinutes(expirationThreshold) < DateTime.Now);

                                if (hasExpired && autoRefresh)
                                {
                                    Character updated = this.SaveAndReturnCharacter(character, realm, false);
                                    if (updated != null)
                                    {
                                        retval = updated;
                                    }
                                    else
                                    {
                                        retval = new Character((String)reader["character"], (String)reader["realm"], (Int16)reader["class"], (Int16)reader["race"], (bool)reader["gender"], (short)reader["player_level"], (int)reader["achievementPoints"], (DateTime)reader["last_modified"], hasExpired, reader["thumbnail"].ToString(), _region);
                                        retval.Guild = this.GetGuild((int)reader["guildID"], expirationThreshold, autoRefresh);
                                        retval.Achievements = this.GetCharacterAchievements((int)reader["id"]);
                                        retval.Titles = this.GetCharacterTitles((int)reader["id"]);
                                    }
                                }
                                else
                                {
                                    retval = new Character((String)reader["character"], (String)reader["realm"], (Int16)reader["class"], (Int16)reader["race"], (bool)reader["gender"], (short)reader["player_level"], (int)reader["achievementPoints"], (DateTime)reader["last_modified"], hasExpired, reader["thumbnaiL"].ToString(), _region);
                                    retval.Guild = this.GetGuild((int)reader["guildID"], expirationThreshold, autoRefresh);
                                    retval.Achievements = this.GetCharacterAchievements((int)reader["id"]);
                                    retval.Titles = this.GetCharacterTitles((int)reader["id"]);
                                }
                            }
                            else
                            {
                                retval = this.SaveAndReturnCharacter(character, realm, true);
                            }
                        }
                    }

                    connection.Close();
                }

                return retval;
            }

            /// <summary>
            /// Creates a Realm object using the Battle.net RESTful API, saves the data into the database and returns the object
            /// </summary>
            /// <param name="name">The name of the realm</param>
            /// <param name="insert">True to insert the record, false to update an existing record</param>
            /// <returns>A Realm</returns>
            private Realm SaveAndReturnRealm(String name, bool insert)
            {
                name = name.Replace("'", String.Empty).Replace("`", String.Empty).Replace(" ", "-");
                WebRequest request = new WebRequest(_region, _locale);
                Realm retval;
                String sqlString = String.Empty;

                // Construct a new realm object based on the latest data
                // from Battle.net
                retval = request.GetRealm(name);

                if (insert)
                {
                    sqlString = "INSERT INTO realms (realm, region, type, queue, status, population, battlegroup, slug, last_refresh) " +
                                "VALUES (@realm, @region, @type, @queue, @status, @population, @battlegroup, @slug, @lastRefresh)";
                }
                else
                {
                    sqlString = "UPDATE realms SET queue = @queue, status = @status, population = @population, last_refresh = @lastRefresh WHERE realm = @realm AND region = @region";
                }

                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand(sqlString, connection))                    {
                        if (insert)
                        {
                            command.Parameters.AddWithValue("@type", retval.Type);
                            command.Parameters.AddWithValue("@battlegroup", retval.Battlegroup);
                            command.Parameters.AddWithValue("@slug", retval.Slug);
                        }

                        command.Parameters.AddWithValue("@realm", retval.Name);
                        command.Parameters.AddWithValue("@region", _region.ToString());
                        command.Parameters.AddWithValue("@queue", retval.HasQueue);
                        command.Parameters.AddWithValue("@status", retval.IsOnline);
                        command.Parameters.AddWithValue("@population", retval.Population);
                        command.Parameters.AddWithValue("@lastRefresh", DateTime.Now);

                        command.ExecuteNonQuery();
                    }

                    connection.Close();
                }

                return retval;
            }

            /// <summary>
            /// Fetch a realm from the database
            /// </summary>
            /// <param name="name">The name of the realm</param>
            /// <param name="expirationThreshold">The cache expiration threshold in minutes</param>
            /// <param name="autoRefresh">True to auto refresh the data if it has expired, or false to not refresh</param>
            /// <returns>A Realm</returns>
            public Realm GetRealm(String name, int expirationThreshold, bool autoRefresh)
            {
                Realm retval = new Realm();
                name = name.Replace("'", String.Empty).Replace("`", String.Empty).Replace(" ", "-");

                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("SELECT * FROM realms WHERE region = @region AND slug = @realm", connection))
                    {
                        command.Parameters.AddWithValue("@region", _region.ToString());
                        command.Parameters.AddWithValue("@realm", name);

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            // If a record exists for this realm and auto-refreshing is enabled refresh the data if it has expired
                            // and construct the object. If no record exists for the realm fetch it via the RESTful API and store it
                            if (reader.Read())
                            {
                                bool hasExpired;

                                hasExpired = (((DateTime)reader["last_refresh"]).AddMinutes(expirationThreshold) < DateTime.Now);

                                if (hasExpired && autoRefresh)
                                {
                                    Realm updated = this.SaveAndReturnRealm(name, false);
                                    if (updated != null)
                                    {
                                        retval = updated;
                                    }
                                    else
                                    {
                                        retval = new Realm((string)reader["type"], (bool)reader["queue"], (bool)reader["status"], (string)reader["population"], (string)reader["realm"], (string)reader["battlegroup"], (string)reader["slug"]);
                                    }
                                }
                                else
                                {
                                    retval = new Realm((string)reader["type"], (bool)reader["queue"], (bool)reader["status"], (string)reader["population"], (string)reader["realm"], (string)reader["battlegroup"], (string)reader["slug"]);
                                }
                            }
                            else
                            {
                                retval = this.SaveAndReturnRealm(name, true);
                            }
                        }
                    }

                    connection.Close();
                }

                return retval;
            }

        #endregion

        #region Constructors

            public DatabaseRequest(String connectionString, BattleNetRegion region, BattleNetLocale locale)
            {
                _connectionString = connectionString;
                _region = region;
                _locale = locale;
            }

        #endregion
    }
}